package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.CzDao;
/**
 * 
 * @author zhai
 * @version 创建时间：2015年12月15日 上午9:34:42
 * @功能：充值结果界面
 */

@Repository
public class CzDaoImpl  extends P2PJdbcDao implements CzDao{
	
	
	/**
	 * 日志
	 */
	Logger logger=Logger.getLogger(JhnhkDaoImpl.class);

	public List<Map<String, Object>> queryCzList(JSONObject params) {

		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		int minczje = params.optInt("minczje");
		int maxczje = params.optInt("maxczje");
		double czje=params.optDouble("czje");
		String czState=params.optString("czState");
		String sjhm=params.optString("sjhm");
		String gyh=params.optString("gyh");
		String tjr=params.optString("tjr");
		String ddh=params.optString("ddh");
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("CZSTATE", czState);
		paramMap.put("MINCZJE", minczje);
		paramMap.put("MAXCZJE", maxczje);
		paramMap.put("CZJE", czje);
		paramMap.put("SJHM", sjhm);
		paramMap.put("GYH", gyh);
		paramMap.put("TJR", tjr);
		paramMap.put("DDH", ddh);
		
		sql.append(" select t1.ID,t1.SHH,t1.DDH,t1.CZJE,date_format(t1.CZSJ,'%Y-%m-%d %H:%i:%s') CZSJ, t2.GYH, t3.SJHM  ");
		sql.append(" 			,t4.REAL_NAME,T4.SFZ_NUM ,T3.TJR from v_p2p_czjg t1   ");
		sql.append(" 		left JOIN   ");
		sql.append(" 		(select distinct gyh, ddh from t_zhfw_czjl) t2 on t1.DDH = t2.DDH  ");
		sql.append(" 		LEFT JOIN T_ZHFW_USER T3 ON t2.GYH = t3.user_name  ");
		sql.append(" 		left join t_zhfw_smrz t4 on t2.GYH = t4.USER_NAME  ");
		sql.append(" 		" );
		sql.append(" where 1=1  ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(t1.CZSJ,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(t1.CZSJ,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND t3.SJHM like "+ "'%"+sjhm+"%'");
		}
		if(StringUtils.isNotEmpty(gyh)){
			sql.append(" AND t2.GYH like "+ "'%"+gyh+"%'");
		}
		if(minczje>0){
	        sql.append("AND t1.CZJE > :MINCZJE ");
		}
	    if(maxczje>0){
			sql.append(" AND t1.CZJE < :MAXCZJE");
		}
		if(StringUtils.isNotEmpty(tjr)){
			sql.append(" AND T3.TJR = :TJR");
			paramMap.put("TJR", tjr);
		}
		if(StringUtils.isNotEmpty(ddh)){
			sql.append(" AND T1.DDH LIKE "+ "'%"+ddh+"%'");
		}
		sql.append(" order by date_format(t1.CZSJ,'%Y-%m-%d %H:%i:%s') desc ");
		if(0 != limit){
			sql.append(" LIMIT :START,:LIMIT");
		}
		logger.info("queryCzList sql is "+sql.toString());
		logger.info("queryCzList paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}

	/**
	 * @author zhai
	 * CzDao 的实现类
	 * 获取充值的数据个数
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getCzCount(JSONObject params) {

		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		int minczje = params.optInt("minczje");
		int maxczje = params.optInt("maxczje");
		double czje=params.optDouble("czje");
		String czState=params.optString("czState");
		String sjhm=params.optString("sjhm");
		String gyh=params.optString("gyh");
		String tjr=params.optString("tjr");
		String ddh=params.optString("ddh");
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("CZSTATE", czState);
		paramMap.put("MINCZJE", minczje);
		paramMap.put("MAXCZJE", maxczje);
		paramMap.put("CZJE", czje);
		paramMap.put("SJHM", sjhm);
		paramMap.put("GYH", gyh);
		paramMap.put("TJR", tjr);
		paramMap.put("DDH", ddh);
		
		sql.append(" select COUNT(*) CT from v_p2p_czjg t1   ");
		sql.append(" 		left JOIN   ");
		sql.append(" 		(select distinct gyh, ddh from t_zhfw_czjl) t2 on t1.DDH = t2.DDH  ");
		sql.append(" 		LEFT JOIN T_ZHFW_USER T3 ON t2.GYH = t3.user_name  ");
		sql.append(" 		left join t_zhfw_smrz t4 on t2.GYH = t4.USER_NAME  ");
		sql.append(" 		" );
		sql.append(" where 1=1  ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(t1.CZSJ,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(t1.CZSJ,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND t3.SJHM like "+ "'%"+sjhm+"%'");
		}
		if(StringUtils.isNotEmpty(gyh)){
			sql.append(" AND t2.GYH like "+ "'%"+gyh+"%'");
		}
		if(minczje>0){
	        sql.append("AND t1.CZJE > :MINCZJE ");
		}
	    if(maxczje>0){
			sql.append(" AND t1.CZJE < :MAXCZJE");
		}
	    if(StringUtils.isNotEmpty(tjr)){
			sql.append(" AND T3.TJR = :TJR");
			paramMap.put("TJR", tjr);
		}
	    if(StringUtils.isNotEmpty(ddh)){
			sql.append(" AND T1.DDH LIKE "+ "'%"+ddh+"%'");
		}
		logger.info("getCzCount sql is "+sql.toString());
		logger.info("getCzCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForObject(
				sql.toString(), paramMap,Integer.class);

	}

	@Override
	public String geCzjeSum(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		int minczje = params.optInt("minczje");
		int maxczje = params.optInt("maxczje");
		double czje=params.optDouble("czje");
		String czState=params.optString("czState");
		String sjhm=params.optString("sjhm");
		String gyh=params.optString("gyh");
		String tjr=params.optString("tjr");
		String ddh=params.optString("ddh");
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("CZSTATE", czState);
		paramMap.put("MINCZJE", minczje);
		paramMap.put("MAXCZJE", maxczje);
		paramMap.put("CZJE", czje);
		paramMap.put("SJHM", sjhm);
		paramMap.put("GYH", gyh);
		paramMap.put("TJR", tjr);
		paramMap.put("DDH", ddh);
		
		sql.append(" select round(ifnull(sum(t1.CZJE),0),2) SUMJE from v_p2p_czjg t1   ");
		sql.append(" 		left JOIN   ");
		sql.append(" 		(select distinct gyh, ddh from t_zhfw_czjl) t2 on t1.DDH = t2.DDH  ");
		sql.append(" 		LEFT JOIN T_ZHFW_USER T3 ON t2.GYH = t3.user_name  ");
		sql.append(" 		left join t_zhfw_smrz t4 on t2.GYH = t4.USER_NAME  ");
		sql.append(" 		" );
		sql.append(" where 1=1  ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(t1.CZSJ,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(t1.CZSJ,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND t3.SJHM like "+ "'%"+sjhm+"%'");
		}
		if(StringUtils.isNotEmpty(gyh)){
			sql.append(" AND t2.GYH like "+ "'%"+gyh+"%'");
		}
		if(minczje>0){
	        sql.append("AND t1.CZJE > :MINCZJE ");
		}
	    if(maxczje>0){
			sql.append(" AND t1.CZJE < :MAXCZJE");
		}
		if(StringUtils.isNotEmpty(tjr)){
			sql.append(" AND T3.TJR = :TJR");
			paramMap.put("TJR", tjr);
		}
		if(StringUtils.isNotEmpty(ddh)){
			sql.append(" AND T1.DDH LIKE "+ "'%"+ddh+"%'");
		}
		logger.info("geCzjeSum sql is "+sql.toString());
		logger.info("geCzjeSum paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String result="0.00";
				while (rs.next()) {
					result=rs.getString("SUMJE");
				}
				return result;
			}
		});

	}


}
